package com.health.dao;

import com.health.pojo.ComboForm;
import com.health.pojo.HealthAttributes;
import com.health.utils.ConnectionUtil;
import com.health.utils.PageInfo;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class HealthDao {

    /**
     * 根据id查询健康属性
     */
    public int selectHealthAttributeById(PageInfo pageInfo, int userId, boolean isPage, String searchExamine) {
        Connection connection = ConnectionUtil.getConnection();
        PreparedStatement preparedStatement = null;
        List<HealthAttributes> list = new ArrayList<>();
        String sql = null;
        String sqlBySearchText = null;
        String sqlBySearchDate = null;
        String searchTempt = null;
        // 对于搜索拼接SQL查询条件
        //
        if (isPage) {
            sql = "select *\n" +
                    "from (\n" +
                    "    select ROW_NUMBER() over (order by user_id() asc) as line, *\n" +
                    "    from users_attribute\n" +
                    "    where user_id = ? )users_attribute,health_attribute\n" +
                    "where users_attribute.attribute_id = health_attribute.attribute_id and line > ? and line <= ? ";
        } else {
            sqlBySearchText = " and attribute_name like ? ";
            sqlBySearchDate = " and examine_date = ? ";
            if ("".equals(searchExamine)) {
                sql = "select *  from ( " +
                        "select ROW_NUMBER() over (order by user_id asc) as line ,attribute_value,attribute_name,attribute_scope,examine_date " +
                        "from users_attribute,health_attribute where user_id = ? " +
                        "and users_attribute.attribute_id = health_attribute.attribute_id " + sqlBySearchText +
                        ")health_attribute where line > ? and line <= ? ";
            } else {
                sql = "select *  from ( " +
                        "select ROW_NUMBER() over (order by user_id asc) as line ,attribute_value,attribute_name,attribute_scope,examine_date " +
                        "from users_attribute,health_attribute where user_id = ? " +
                        "and users_attribute.attribute_id = health_attribute.attribute_id " + sqlBySearchText + sqlBySearchDate +
                        ")health_attribute where line > ? and line <= ? ";
            }
        }
        try {
            preparedStatement = connection.prepareStatement(sql);
            if (isPage) {
                preparedStatement.setInt(1, userId);
                // 设置分页数据
                preparedStatement.setInt(2, ((pageInfo.getCurrentPage() - 1) * pageInfo.getPageSize()));
                preparedStatement.setInt(3, (pageInfo.getCurrentPage()) * pageInfo.getPageSize());
            } else {
                preparedStatement.setInt(1, userId);
                searchTempt = "%" + pageInfo.getSearchValue() + "%";
                preparedStatement.setString(2, searchTempt);
                if ("".equals(searchExamine)) {
                    // 设置分页数据
                    preparedStatement.setInt(3, ((pageInfo.getCurrentPage() - 1) * pageInfo.getPageSize()));
                    preparedStatement.setInt(4, (pageInfo.getCurrentPage()) * pageInfo.getPageSize());
                } else {
                    preparedStatement.setDate(3, Date.valueOf(searchExamine));  // 转换为date类型
                    // 设置分页数据
                    preparedStatement.setInt(4, ((pageInfo.getCurrentPage() - 1) * pageInfo.getPageSize()));
                    preparedStatement.setInt(5, (pageInfo.getCurrentPage()) * pageInfo.getPageSize());
                }
            }

            ResultSet resultSet = preparedStatement.executeQuery();

            int attributeId = 0;
            while (resultSet.next()) {
                if (isPage) {
                    attributeId = resultSet.getInt("attribute_id");
                }
                Double attributeValue = resultSet.getDouble("attribute_value");
                String attributeName = resultSet.getString("attribute_name");
                String attributeScope = resultSet.getString("attribute_scope");
                String examineDate = resultSet.getDate("examine_date").toString();
                HealthAttributes healthAttributes = new HealthAttributes(attributeId, attributeName, attributeScope, attributeValue, examineDate);
                list.add(healthAttributes);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        int count = 0;
        String queryAll = null;
        if (isPage) {
            queryAll = "select count(*) as count from users_attribute,health_attribute\n" +
                    "where users_attribute.attribute_id = health_attribute.attribute_id and\n" +
                    "        user_id = ?";
        } else {
            if ("".equals(searchExamine)) {
                queryAll = "select count(*) as count from users_attribute,health_attribute\n" +
                        "where users_attribute.attribute_id = health_attribute.attribute_id and\n" +
                        "        user_id = ? and attribute_name like ?";
            } else {
                queryAll = "select count(*) as count from users_attribute,health_attribute\n" +
                        "where users_attribute.attribute_id = health_attribute.attribute_id and\n" +
                        "        user_id = ? and attribute_name like ? and examine_date = ?";
            }

        }

        try {
            PreparedStatement preparedStatement2 = connection.prepareStatement(queryAll);
            preparedStatement2.setInt(1, userId);
            if (!isPage) {
                preparedStatement2.setString(2, "%" + pageInfo.getSearchValue() + "%");
                if (!("".equals(searchExamine))) {
                    preparedStatement2.setDate(3, Date.valueOf(searchExamine));
                }
            }
            ResultSet resultSet = preparedStatement2.executeQuery();
            if (resultSet.next()) {
                count = resultSet.getInt("count");
            }
            pageInfo.setTotalCount(count);
        } catch (Exception e) {
            e.printStackTrace();
            return -1;
        }
        pageInfo.setRecords(list);
        pageInfo.setSearchExamineDate(searchExamine);
        return 1;

    }

    //查询健康属性表
    public void selecthealthAttributes(ComboForm comboForm) {
        Connection connection = ConnectionUtil.getConnection();
        String sqlstring = "select * from health_attribute ";
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement(sqlstring);
            rs = ps.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("attribute_id");
                String name = rs.getString("attribute_name");
                String scope = rs.getString("attribute_scope");
                HealthAttributes healthAttributes = new HealthAttributes(id, name, scope);
                System.out.println(id + name + scope);
                comboForm.getHealthlist().put(Integer.toString(id), healthAttributes);
            }

        } catch (Exception e) {
            e.printStackTrace();

        }
    }


}
